package src.main.java.com.pms.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {
    private static final Properties properties = new Properties();

    static {
        try (InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties")) {
            if (is == null) {
                throw new RuntimeException("db.properties not found in classpath");
            }
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // JDBC驱动名及数据库URL
    private static final String JDBC_DRIVER = properties.getProperty("jdbc.driver");
    private static final String DB_URL = properties.getProperty("db.url");

    // 数据库的用户名与密码
    private static final String USER = properties.getProperty("db.user");
    private static final String PASS = properties.getProperty("db.password");

    /**
     * 获取数据库连接
     *
     * @return Connection 对象
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        Class.forName(JDBC_DRIVER);
        return DriverManager.getConnection(DB_URL, USER, PASS);
    }

    /**
     * 关闭数据库连接
     *
     * @param conn 数据库连接对象
     * @param stmt Statement对象
     * @param rs   ResultSet对象
     */
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行SQL查询
     *
     * @param sql SQL查询语句
     * @return ResultSet 结果集
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static ResultSet executeQuery(String sql) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 执行SQL更新
     *
     * @param sql SQL更新语句
     * @return 受影响的行数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static int executeUpdate(String sql) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            return stmt.executeUpdate(sql);
        } finally {
            close(conn, stmt, null);
        }
    }

    public static ResultSet executeQueryWithConnection(String sql, Object... params) throws SQLException, ClassNotFoundException {
        Connection conn = getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            pstmt.setObject(i + 1, params[i]);
        }
        return pstmt.executeQuery();
    }

    /**
     * 执行带参数的SQL更新
     *
     * @param sql SQL更新语句
     * @param params 参数数组
     * @return 受影响的行数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static int executeUpdateWithParams(String sql, Object... params) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            return pstmt.executeUpdate();
        } finally {
            close(conn, pstmt, null);
        }
    }
}
